A Parameter object represents a parameter in the parameterized command or stored procedure on which a Command object is based. In theory, a provider might not support parameterized commands, but in practice, all major providers do. Parameter objects can represent input values for a command or a query or output values or return values from a stored procedure. All the Parameter objects related to a Command object are contained in the Command's Parameters collection.
ADO is really smart at dealing with the Parameters collection. It automatically builds the collection when you reference the Parameters property of a Command object. But ADO also gives you the capability to create the collection yourself through code, which is something that isn't possible in DAO or RDO. Usually you can get better performance if you create the Parameters collection yourself because you save ADO a round-trip to the server just to determine the names and the types of all the parameters. On the other hand, if you want ADO to retrieve all the parameters' names and attributes, you only have to perform the Refresh method of the Parameters collection of a Command object, as here:
cmd.Parameters.Refresh |
Calling the Refresh method is optional, however, because if you access the Parameters collection without having created its elements yourself, ADO will refresh the collection automatically.
The Parameter object exposes nine properties. Most of them are similar to properties with the same names as those exposed by the Field object. Because of the similarities, I won't describe these properties in depth here. For example, each Parameter object has a Name, Type, Precision, and NumericScale property, exactly as Field objects have. Table 13-5 lists all the possible values for the Type property. (Notice that Parameter objects support a few types that Field objects don't support.)
The Parameter object also supports the Value property. This property is also the default for this object, so you can omit it if you want to:
cmd.Parameters("StartHireDate") = #1/1/1994#
|
The Direction property specifies whether the Parameter object represents an input parameter, an output parameter, or the return value from a stored procedure. It can be one of the following enumerated constants:
| Value | Description |
|---|---|
| 0-adParamUnknown | Unknown direction |
| 1-adParamInput | An input parameter (the default) |
| 2-adParamOutput | An output parameter |
| 3-adParamInputOutput | An input/output parameter |
| 4-adParamReturnValue | A return value from a stored procedure |
This property is read/write, which is useful when you're working with a provider that can't determine the direction of the parameters in a stored procedure.
The Attributes property specifies a few characteristics of the Parameter object. This is a bit-field value that can be the sum of the following values:
| Value | Description |
|---|---|
| 16-adParamSigned | The parameter accepts signed values. |
| 64-adParamNullable | The parameter accepts Null values. |
| 128-adParamLong | The parameter accepts long binary data. |
The Size property sets and returns the maximum size of the value of a Parameter object. If you're creating a Parameter object of a variable-length data type (for example, a string type), you must set this property before appending the parameter to the Parameters collection; if you don't, an error occurs. If you've already appended the Parameter object to the Parameters collection and you later change its type to a variable-length data type, you must set the Size property before invoking the Execute method.
The Size property is also useful if you let the provider automatically populate the Parameters collection. When the collection includes one or more variable-length items, ADO can allocate memory for those parameters based on their maximum potential dimension, which might cause an error later. You can prevent such errors by explicitly setting the Size property to the correct value before executing the command.
The only method the Parameter object supports is AppendChunk. This method works exactly as it does in the Field object, so I won't repeat its description here. You can test the adParamLong bit of the Parameter object's Attributes property to test whether the parameter supports this method.
Each Command object exposes a Parameters property that returns a reference to a Parameters collection. As I mentioned earlier in the chapter, you can let ADO automatically populate this collection or you can save ADO some work by creating Parameter objects and adding them manually to the collection. You add objects manually with the CreateParameter method of the Command object in conjunction with the Append method of the Parameters collection, as this code demonstrates:
' Edit this constant to match your directory structure.
Const DBPATH = "C:\Program Files\Microsoft Visual Studio\Vb98\NWind.mdb"
Dim cmd As New ADODB.Command, rs As New ADODB.Recordset
cmd.CommandText = "Select * From Employees Where BirthDate > ? " _
& "AND HireDate > ?"
cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source= " & DBPATH
' You can use a temporary Parameter variable.
Dim param As ADODB.Parameter
Set param = cmd.CreateParameter("BirthDate", adDate, , , #1/1/1960#)
cmd.Parameters.Append param
' Or you can do everything in one operation.
cmd.Parameters.Append cmd.CreateParameter("HireDate", adDate, , , _
#1/1/1993#)
Set rs = cmd.Execute(, , adCmdText)
|
Parameterized queries and commands are especially useful when you're going to perform the operation more than once. In all subsequent operations, you need to modify only the values of the parameters:
' You can reference a parameter by its index in the collection.
cmd.Parameters(0) = #1/1/1920#
' But you deliver more readable code if you reference it by its name.
cmd.Parameters("HireDate") = #1/1/1920#
Set rs = cmd.Execute()
|
You can use the Delete method of the Parameters collection to remove items from it, and you can use its Count property to determine how many elements it contains. When the Command object refers to a stored procedure that has a return value, Parameters(0) always refers to the return value.